from flask import Blueprint, render_template, request
from datetime import datetime
from database import get_db
from flask import make_response
import pandas as pd
from io import BytesIO
from reportlab.lib import colors
from reportlab.pdfbase import pdfmetrics
from reportlab.pdfbase.ttfonts import TTFont

report_thermal_bp = Blueprint('report_thermal', __name__, url_prefix='/report/thermal')

@report_thermal_bp.route('/')
def index():
    # 固定默认值：2025年和珠海基地
    selected_year = 2025
    organization_boundary = "珠海基地"
    # 传递固定参数调用报告数据函数
    report_data = get_report_data(selected_year, organization_boundary)
    # 渲染模板时传递默认值
    return render_template('report_thermal.html', 
                         report_data=report_data,
                         selected_year=selected_year,
                         selected_organization=organization_boundary)

def get_report_data(year=2025, organization_boundary=None):
    db = get_db()

    # 构建查询条件
    query_params = [year]
    where_clauses = ['ci.belong_year = ?']
    
    # 添加组织边界筛选条件
    if organization_boundary:
        where_clauses.append('ci.organization_boundary = ?')
        query_params.append(organization_boundary)
    thermal_sql = f'''
        SELECT SUM(ies.activity_data) as thermal_activity,
            cf.name as thermal_factor,
            SUM(ies.activity_data * cf.emission_value) as thermal_emission
        FROM inventory_emission_sources ies
        JOIN carbon_inventory ci ON ies.inventory_id = ci.id
        JOIN models m ON ies.emission_source_id = m.id
        JOIN carbon_factors cf ON m.factor_id = cf.id
        WHERE {' AND '.join(where_clauses)}
        AND cf.name LIKE '%热力%' AND cf.name LIKE '%广东%' and m.emission_source LIKE '%购入%'
    '''
    thermal_results = db.execute(thermal_sql, query_params).fetchone()
    
    # 先删除表中该年份的旧数据
    db.execute("DELETE FROM report_thermal WHERE year = ?", (year,))
    
    # 插入新数据
    db.execute(
        """INSERT INTO report_thermal (
            year, purchase_gj, purchase_factor, purchase_emission
        ) VALUES (?, ?, ?, ?)""",
        (
            year,
            thermal_results['thermal_activity'],  # thermal_activity
            thermal_results['thermal_factor'],  # thermal_factor  
            thermal_results['thermal_emission'],  # thermal_emission    
        )
    )
    db.commit()

    thermal_out_sql = f'''
        SELECT SUM(ies.activity_data) as thermal_out_activity,
            cf.name as thermal_out_factor,
            SUM(ies.activity_data * cf.emission_value) as thermal_out_emission
        FROM inventory_emission_sources ies
        JOIN carbon_inventory ci ON ies.inventory_id = ci.id
        JOIN models m ON ies.emission_source_id = m.id
        JOIN carbon_factors cf ON m.factor_id = cf.id
        WHERE {' AND '.join(where_clauses)}
        AND cf.name LIKE '%热力%' AND cf.name LIKE '%广东%' and m.emission_source LIKE '%输出%'
    '''
    thermal_out_results = db.execute(thermal_out_sql, query_params).fetchone()
    
    # 插入新数据
    db.execute(
        """INSERT INTO report_thermal (
            year, output_gj, output_factor, output_emission
        ) VALUES (?, ?, ?, ?)""",
        (
            year,
            thermal_out_results['thermal_out_activity'],  # thermal_out_activity
            thermal_out_results['thermal_out_factor'],  # thermal_out_factor  
            thermal_out_results['thermal_out_emission'],  # thermal_out_emission    
        )
    )
    db.commit()

    thermal_sql = f'''
        SELECT year,
            purchase_gj,
            purchase_factor,
            purchase_emission,
            output_gj,
            output_factor,
            output_emission
            FROM report_thermal 
            WHERE year = {year}
    '''
    thermal_results = db.execute(thermal_sql).fetchall()
    return [{
        'purchase_gj': row['purchase_gj'],
        'purchase_factor': row['purchase_factor'],
        'purchase_emission': row['purchase_emission'],
        'output_gj': row['output_gj'],
        'output_factor': row['output_factor'],
        'output_emission': row['output_emission'],
    }for row in thermal_results]




@report_thermal_bp.route('/export/<format>')
def export(format):
    report_data = get_report_data()
    df = pd.DataFrame(report_data)
    
    if format == 'excel':
        output = BytesIO()
        writer = pd.ExcelWriter(output, engine='xlsxwriter')
        df.to_excel(writer, sheet_name='热力报表', index=False)
        writer.close()
        output.seek(0)
        response = make_response(output.getvalue())
        response.headers['Content-Type'] = 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
        response.headers['Content-Disposition'] = 'attachment; filename=thermal_report.xlsx'
        return response
    
    elif format == 'pdf':
        from reportlab.pdfgen import canvas
        from reportlab.lib.pagesizes import letter
        from reportlab.platypus import SimpleDocTemplate, Table, TableStyle, Paragraph
        from reportlab.lib.styles import getSampleStyleSheet
        
        pdfmetrics.registerFont(TTFont('SimSun', 'simsun.ttc'))
        
        output = BytesIO()
        doc = SimpleDocTemplate(output, pagesize=letter)
        
        data = [
            ['项目', '热量(GJ)', '排放因子(tCO₂/GJ)', '排放量(tCO₂)']
        ]
        # 直接访问单条记录（移除for循环）
        item = report_data[0] if isinstance(report_data, list) else report_data
        
        data.append([
        '购入',
        item.get('purchase_gj', ''),
        item.get('purchase_factor', ''),
        item.get('purchase_emission', '')
        ])
        data.append([
        '输出',
        item.get('output_gj', ''),
        item.get('output_factor', ''),
        item.get('output_emission', '')
        ])
        styles = getSampleStyleSheet()
        styles['Title'].fontName = 'SimSun'
        title = Paragraph('热力消耗报表', styles['Title'])
        
        table = Table(data)
        table.setStyle(TableStyle([
            ('BACKGROUND', (0,0), (-1,0), colors.grey),
            ('TEXTCOLOR', (0,0), (-1,0), colors.whitesmoke),
            ('ALIGN', (0,0), (-1,-1), 'CENTER'),
            ('FONTNAME', (0,0), (-1,-1), 'SimSun'),
            ('FONTSIZE', (0,0), (-1,0), 14),
            ('BOTTOMPADDING', (0,0), (-1,0), 12),
            ('BACKGROUND', (0,1), (-1,-1), colors.white),
            ('GRID', (0,0), (-1,-1), 1, colors.black)
        ]))
        
        elements = [title, table]
        doc.build(elements)
        
        output.seek(0)
        response = make_response(output.getvalue())
        response.headers['Content-Type'] = 'application/pdf'
        response.headers['Content-Disposition'] = 'attachment; filename=thermal_report.pdf'
        return response
    
    elif format == 'word':
        from docx import Document
        from docx.shared import Inches, Pt
        from docx.enum.text import WD_ALIGN_PARAGRAPH
        
        output = BytesIO()
        doc = Document()
        
        title = doc.add_paragraph('热力消耗报表')
        title.alignment = WD_ALIGN_PARAGRAPH.CENTER
        title.runs[0].font.size = Pt(16)
        title.runs[0].bold = True
        title.runs[0].font.name = 'Microsoft YaHei'
        
        table = doc.add_table(rows=1, cols=4)
        table.style = 'Table Grid'
        
        hdr_cells = table.rows[0].cells
        hdr_cells[0].text = '项目'
        hdr_cells[1].text = '热量(GJ)'
        hdr_cells[2].text = '排放因子(tCO₂/GJ)'
        hdr_cells[3].text = '排放量(tCO₂)'
        
        for item in report_data:
            row = table.add_row().cells
            row[0].text = '购入'
            row[1].text = str(item.get('purchase_gj', ''))
            row[2].text = str(item.get('purchase_factor', ''))
            row[3].text = str(item.get('purchase_emission', ''))
            
            row = table.add_row().cells
            row[0].text = '输出'
            row[1].text = str(item.get('output_gj', ''))
            row[2].text = str(item.get('output_factor', ''))
            row[3].text = str(item.get('output_emission', ''))
        
        doc.save(output)
        output.seek(0)
        
        response = make_response(output.getvalue())
        response.headers['Content-Type'] = 'application/vnd.openxmlformats-officedocument.wordprocessingml.document'
        response.headers['Content-Disposition'] = 'attachment; filename=thermal_report.docx'
        return response